Excel BI - Excel Challenge 779

🔰 The displayed result is the outcome of executing 50,000 sets.

Illustration for Excel BI - Excel Challenge 779

Challenge Description

🔰 The displayed result is the outcome of executing 50,000 sets. You should execute it minimum number of times to achieve the same result.

Solutions

R Solution

library(tidyverse)
library(readxl)

path = "Excel/700-799/779/779 Linked_in_Block_Shift.xlsx"
input = read_excel(path, range = "B2:F4", col_names = F) %>% as.matrix()
col_rotations = read_excel(path, range = "B1:F1", col_names = F) %>% as.numeric()
row_rotations = read_excel(path, range = "A2:A4", col_names = F) %>% pull()
test  = read_excel(path, range = "H2:L4", col_names = F) %>% as.matrix()


rotate_vec = function(vec, n) {
  n = n %% length(vec)
  c(tail(vec, n), head(vec, length(vec) - n))
}
apply_rotations = function(mat, row_rotations, col_rotations) {
  mat = map2(1:nrow(mat), row_rotations, ~ rotate_vec(mat[.x, ], .y)) %>%
    reduce(rbind)
  mat = map2(1:ncol(mat), col_rotations, ~ rotate_vec(mat[, .x], .y)) %>%
    reduce(cbind)
  mat
}
find_iteration = function(start_mat, target_mat, row_rotations, col_rotations, max_iter = 1000) {
  mat = start_mat
  for (i in 1:max_iter) {
    mat = apply_rotations(mat, row_rotations, col_rotations)
    if (all(mat == target_mat)) return(i)
  }
  NA
}

# find occuirrence of the test matrix in the iterations
result = find_iteration(input, test, row_rotations, col_rotations) # 32
result

# checking if after 50k iterations the result is the same as the test matrix
result_50000 = input
for (i in 1:50000) {
  result_50000 = apply_rotations(result_50000, row_rotations, col_rotations)
}
all.equal(result_50000, test, check.attributes = FALSE) # TRUE
# The result after 50000 iterations is the same as the test matrix

R Analysis

• Logic: Read the workbook ranges needed for the challenge; Iterate through the sequence until the rule is satisfied.
• Strengths: The algorithm is explicit about the sequence rule, so the control flow is easy to validate against the prompt.
• Areas for Improvement: The solution assumes the workbook layout and selected ranges remain stable, so any structural change in the sheet would require small adjustments.
• Gem: The non-obvious part is the local rule inside the loop, because that rule determines the whole output.

Python Solution

import numpy as np
import pandas as pd

path = "700-799/779/779 Linked_in_Block_Shift.xlsx"

input_mat = pd.read_excel(path, header=None, usecols="B:F", skiprows=1, nrows=3).to_numpy()
col_rot = pd.read_excel(path, header=None, usecols="B:F", nrows=1).to_numpy(dtype=int).flatten()
row_rot = pd.read_excel(path, header=None, usecols="A", skiprows=1, nrows=3).to_numpy(dtype=int).flatten()
test_mat = pd.read_excel(path, header=None, usecols="H:L", skiprows=1, nrows=3).to_numpy()

def apply_rotations(mat, row_rot, col_rot):
    mat = np.array([np.roll(row, shift) for row, shift in zip(mat, row_rot)])
    mat = np.array([np.roll(col, shift) for col, shift in zip(mat.T, col_rot)]).T
    return mat

def find_iteration(start, target, row_rot, col_rot, max_iter=1000):
    mat = start.copy()
    for i in range(1, max_iter + 1):
        mat = apply_rotations(mat, row_rot, col_rot)
        if np.array_equal(mat, target): return i
    return None

# Minimal iteratrions to reach the target matrix
print(find_iteration(input_mat, test_mat, row_rot, col_rot))
# 32

# Check if 50k iterations yield the same result
result_50000 = input_mat.copy()
for _ in range(50000):
    result_50000 = apply_rotations(result_50000, row_rot, col_rot)
print(np.array_equal(result_50000, test_mat))
# True

Python

The Python version keeps the algorithm explicit, which helps when the challenge depends on a greedy or iterative rule.

Difficulty Level

Easy / Medium

The business rule is clear, though the workbook still needs a few transformation steps to reach the expected output.

Related Challenges

• Excel Challenge 780
• Excel Challenge 781
• Excel Challenge 782